| CONTENTS |
In the summer of 1996, Sun released the first version of the Java Database Connectivity (JDBC) kit. This package lets programmers connect to a database, query it, or update it, using the Structured Query Language or SQL. (SQL, usually pronounced like "sequel," is an industry standard for database access.) When JDBC was first announced, we considered this one of the most important developments in programming for the Java platform. It is not just that databases are among the most common use of hardware and software today. After all, there are a lot of products running after this market, so why did we think the Java programming language had the potential to make a big splash? The reason that Java and JDBC have an essential advantage over other database programming environments is this:
Programs developed with the Java programming language and JDBC are platform independent and vendor independent.
The same database program written in the Java programming language can run on an NT box, a Solaris server, or a database appliance powered by the Java platform. You can move your data from one database to another, for example, from Microsoft SQL Server to Oracle, or even to a tiny database embedded in a device, and the same program can still read your data. This is in sharp contrast to traditional database programming. It is all too common that one writes database applications in a proprietary database language, using a database management system that is available only from a single vendor. The result is that you can run the resulting application only on one or two platforms. We believe that because of their universality, the Java programming language and JDBC will eventually replace proprietary database languages and call level interfaces used by vendors such as Oracle, Informix, and Microsoft for accessing databases.
NOTE
![]() |
Some database vendors now build a Java Virtual Machine into the database itself so that you can write stored procedures in Java. This technology is part of the SQLJ specification. For more information on SQLJ, see http://www.sqlj.org. |
As part of the release of Java 2 in 1998, a second version of JDBC was issued as well. At the time of this writing, support for JDBC 2 is still not universal, but many JDBC 2 drivers are available. JDBC 2 introduces several major features such as scrollable cursors and support for advanced SQL types. The JDBC 3 specification was in its final review phase as this chapter was finished. JDBC 3 enhancements are more technical in nature. In this chapter, we alert you when you need to be aware of the version differences.
We still must caution you that the JDK offers no tools for database programming with the Java programming language. For form designers, query builders, and report generators, you need to turn to third-party packages. "Corporate" or "professional" versions of development environments for the Java platform, such as Visual Café and JBuilder, ship with database integration tools.
In this chapter:
We explain some of the ideas behind JDBC—the "Java Database Connectivity" API.
We give you an introduction (or a refresher) in SQL, the industry-standard Structured Query Language for databases.
We provide enough details and examples so that you can get started in using JDBC for common programming situations.
NOTE
![]() |
Over the years, many technologies were invented to make database access more efficient and fail-safe. Standard databases support indexes, triggers, stored procedures, and transaction management. JDBC supports all these features, but we do not discuss them in detail in this chapter. One could write an entire book on advanced database programming for the Java platform, and many such books have been written. The material in this chapter will give you enough information to effectively use JDBC with a departmental database. To go further with JDBC, we suggest JDBC API Tutorial and Reference by Seth White, Maydene Fisher, Rick Cattell, Graham Hamilton, and Mark Hapner [Addison-Wesley 1999]. |
From the start, the developers of the Java technology at Sun were aware of the potential that Java showed for working with databases. Starting in 1995, they began working on extending the standard Java library to deal with SQL access to databases. What they first hoped to do was to extend Java so that it could talk to any random database, using only "pure" Java. It didn't take them very long to realize that this is an impossible task: there are simply too many databases out there, using too many protocols. Moreover, while database vendors were all in favor of Sun providing a standard network protocol for database access, they were only in favor of it if Sun decided to use their network protocol.
What all the database vendors and tool vendors did agree on was that it would be useful if Sun provided a pure Java API for SQL access along with a driver manager to allow third-party drivers to connect to specific databases. Database vendors could provide their own drivers to plug into the driver manager. There would then be a simple mechanism for registering third-party drivers with the driver manager—the point being that all the drivers needed to do was follow the requirements laid out in the driver manager API.
After a fairly long period of public discussion, the API for database access became the JDBC API, and the rules for writing drivers were encapsulated in the JDBC service provider interface, or SPI. (The SPI is of interest only to database vendors and database tool providers; we don't cover it here.)
This protocol follows the very successful model of Microsoft's ODBC, which provided a C programming language interface for database access. Both JDBC and ODBC are based on the same idea: Programs written according to the JDBC API would talk to the JDBC driver manager, which, in turn, would use the drivers that were plugged into it at that moment to talk to the actual database.
NOTE
![]() |
A list of JDBC drivers currently available can be found at the web site http://industry.java.sun.com/products/jdbc/drivers |
More precisely, the JDBC consists of two layers. The top layer is the JDBC API. This API communicates with the JDBC manager driver API, sending it the various SQL statements. The manager should (transparently to the programmer) communicate with the various third-party drivers that actually connect to the database and return the information from the query or perform the action specified by the query.
NOTE
![]() |
The JDBC specification will actually allow you to pass any string to the underlying driver. The driver can pass this string to the database. This feature allows you to use specialized versions of SQL that may be supported by the driver and its associated database. |
All this means the Java/JDBC layer is all that most programmers will ever have to deal with. Figure 4-1 illustrates what happens.

JDBC drivers are classified into the following types:
A type 1 driver translates JDBC to ODBC and relies on an ODBC driver to communicate with the database. Sun includes one such driver, the JDBC/ODBC bridge, with the JDK. However, the bridge requires deployment and proper configuration of an ODBC driver. When JDBC was first released, the bridge was handy for testing, but it was never intended for production use. At this point, there are plenty of better drivers available, and we advise against using the JDBC/ODBC bridge.
A type 2 driver is a driver, written partly in the Java programming language and partly in native code, that communicates with the client API of a database. When you use such a driver, you must install some platform-specific code in addition to a Java library.
A type 3 driver is a pure Java client library that uses a database-independent protocol to communicate database requests to a server component, which then translates the requests into a database-specific protocol. The client library is independent of the actual database, thus simplifying deployment.
A type 4 driver is a pure Java library that translates JDBC requests directly to a database-specific protocol.
Most database vendors supply either a type 3 or type 4 driver with their database. Furthermore, a number of third-party companies specialize in producing drivers with better standards conformance, support for more platforms, better performance, or, in some cases, simply better reliability than the drivers that are provided by the database vendors.
In summary, the ultimate goal of the JDBC is to make possible the following:
Programmers can write applications in the Java programming language to access any database, using standard SQL statements—or even specialized extensions of SQL—while still following Java language conventions. (All JDBC drivers must support at least the entry-level version of SQL 92.)
Database vendors and database tool vendors can supply the low-level drivers. Thus, they can optimize their drivers for their specific products.
NOTE
![]() |
If you are curious as to why Sun just didn't adopt the ODBC model, their response, as given at the JavaOne conference in May 1996, was:
|
You can use JDBC in both applications and applets. In an applet, all the normal security restrictions apply. By default, the security manager assumes that all applets written in the Java programming language are untrusted.
In particular, applets that use JDBC are only able to open a database connection to the server from which they are downloaded. That means the Web server and the database server must be the same machine, which is not a typical setup. Of course, the Web server can have a proxy service that routes database traffic to another machine. With signed applets, this restriction can be loosened.
Applications, on the other hand, have complete freedom to access remote database servers. If you implement a traditional client/server program (see Figure 4-2), it probably makes more sense to use an application, not an applet, for database access.

However, the world is moving away from client/server and toward a "three-tier model" or even more advanced "n-tier models." In the three-tier model, the client does not make database calls. Instead, it calls on a middleware layer on the server that in turn makes the database queries. The three-tier model has a couple of advantages. It separates visual presentation (on the client) from the business logic (in the middle tier) and the raw data (in the database). Therefore, it becomes possible to access the same data and the same business rules from multiple clients, such as a Java application or applet or a web form.
Communication between the client and middle tier can occur through HTTP (when you use a web browser as the client), RMI (when you use an application or applet—see Chapter 5), or another mechanism. JDBC is used to manage the communication between the middle tier and the back-end database. Figure 4-3 shows the basic architecture. There are, of course, many variations of this model. In particular, the Java 2 Enterprise Edition defines a structure for application servers that manage code modules called Enterprise JavaBeans, and provide valuable services such as load balancing, request caching, security, and simple database access. In that architecture, JDBC still plays an important role for issuing complex database queries. (For more information on the Enterprise Edition, see http://java.sun.com/j2ee.)

JDBC is an interface to SQL, which is the interface to essentially all modern relational databases. Desktop databases usually have a graphical user interfaces that lets users manipulate the data directly, but server-based databases are accessed purely through SQL. Most desktop databases have a SQL interface as well, but it often does not support the full range of ANSI SQL92 features, the current standard for SQL.
The JDBC package can be thought of as nothing more than an application programming interface (API) for communicating SQL statements to databases. We will give a short introduction to SQL in this section. If you have never seen SQL before, you may not find this material sufficient. If so, you should turn to one of the many books on the topic. We recommend Client/Server Databases by James Martin and Joe Leben [Prentice-Hall 1998] or the venerable and opinionated book A Guide to the SQL Standard by Chris. J. Date [Addison-Wesley 1997].
You can think of a database as a bunch of named tables with rows and columns. Each column has a column name. The rows contain the actual data. These are sometimes called records.
As the example database for this book, we use a set of tables that describe a collection of books on HTML. (Thanks to Cye H. Waldman at http://wwwiz.com/books for supplying the sample data.)
| Author_ID | Name | URL |
|---|---|---|
| ARON | Aronson, Larry | http://… |
| ARPA | Arpajian, Scott | http://… |
| … | … | … |
| Title | ISBN | Publisher_ID | URL | Price |
|---|---|---|---|---|
| Beyond HTML | 0-07-882198-3 | 00788 | http://… | 27.95 |
| 10 Minute Guide to HTML | 0-78970541-9 | 07897 | http://… | 15.00 |
| … | … | … | … | … |
| ISBN | Author_ID | Seq_No |
|---|---|---|
| 1-56-884454-9 | TAYL | 1 |
| 1-56884645-2 | SMIT | 1 |
| 1-56884645-2 | BEBA | 2 |
| … | … | … |
| Publisher_ID | Name | URL |
|---|---|---|
| 01262 | Academic Press | www.apnet.com/ |
| 18835 | Coriolis | www.coriolis.com/ |
| … | … | … |
Figure 4-4 shows a view of the Books table. Figure 4-5 shows the result of joining this table with a table of publishers. Both the Books and the Publishers table contain a numerical code for the publisher. When we join both tables on the publisher code, we obtain a query result made up of values from the joined tables. Each row in the result contains the information about a book, together with the publisher name and Web page URL. Note that the publisher names and URLs are duplicated across several rows since we have several rows with the same publisher.


The benefit of joining tables is to avoid unnecessary duplication of data in the database tables. For example, a naive database design might have had columns for the publisher name and URL right in the Books table. But then the database itself, and not just the query result, would have many duplicates of these entries. If a publisher's Web address changed, all entries would need to be updated. Clearly, this is somewhat error prone. In the relational model, we distribute data into multiple tables such that no information is ever unnecessarily duplicated. For example, each publisher URL is contained only once in the publisher table. If the information needs to be combined, then the tables are joined.
In the figures, you can see a graphical tool to inspect and link the tables. Many vendors have tools to express queries in a simple form by connecting column names and filling information into forms. Such tools are often called query by example (QBE) tools. In contrast, a query that uses SQL is written out in text, using the SQL syntax. For example:
SELECT Books.ISBN, Books.Price, Books.Title, Books.Publisher_Id, Publishers.Name, Publishers.URL FROM Books, Publishers WHERE Books.Publisher_Id = Publishers.Publisher_Id
In the remainder of this section, you will learn how to write such queries. If you are already familiar with SQL, just skip this section.
By convention, SQL keywords are written in all caps, although this is not necessary.
The SELECT operation is quite flexible. You can simply select all elements in the Books table with the following query:
SELECT * FROM Books
The FROM statement is required in every SQL SELECT statement. The FROM clause tells the database which tables to examine to find the data.
You can choose the columns that you want.
SELECT ISBN, Price, Title FROM Books
You can restrict the rows in the answer with the WHERE clause.
SELECT ISBN, Price, Title FROM Books WHERE Price <= 29.95
Be careful with the "equals" comparison. SQL uses = and <>, not == or !=, as in the Java programming language, for equality testing.
NOTE
![]() |
Some database vendors support the use of != for inequality testing. This is not standard SQL, so we recommend against using it. |
The WHERE clause can also use pattern matching, using the LIKE operator. The wildcard characters are not the usual * and ?, however. Use a % for zero or more characters and an underscore for a single character. For example:
SELECT ISBN, Price, Title FROM Books WHERE Title NOT LIKE '%H_M%'
Note that strings are enclosed in single quotes, not double quotes. A single quote inside a string is denoted as a pair of single quotes. For example,
SELECT Title FROM Books WHERE Books.Title LIKE '%''%'
reports all titles that contain a single quote.
You can select data from multiple tables.
SELECT * FROM Books, Publishers
Without a WHERE clause, this query is not very interesting. It lists all combinations of rows from both tables. In our case, where Books has 37 rows and Publishers has 18 rows, the result is a table with 37 x 18 entries and lots of duplications. We really want to constrain the query to say that we are only interested in matching books with their publishers.
SELECT * FROM Books, Publishers WHERE Books.Publisher_Id = Publishers.Publisher_Id
This query result has 37 rows, one for each book, since each book has a publisher in the Publisher table.
Whenever you have multiple tables in a query, the same column name can occur in two different places. That happened in our example. There is a publisher code column called Publisher_Id in both the Books and the Publishers table. When an ambiguity would otherwise result, you must prefix each column name with the name of the table to which it belongs, such as Books.Publisher_Id.
Now you have seen all SQL constructs that were used in the query at the beginning of this section:
SELECT Books.ISBN, Books.Price, Books.Title, Books.Publisher_Id, Publishers.Name, Publishers.URL FROM Books, Publishers WHERE Books.Publisher_Id = Publishers.Publisher_Id
You can use SQL to change the data inside a database as well, by using so-called action queries (i.e., queries that move or change data). For example, suppose you want to reduce by $5.00 the current price of all books that do not have "HTML 3" in their title.
UPDATE Books SET Price = Price - 5.00 WHERE Title NOT LIKE '%HTML 3%'
Similarly, you can change several fields at the same time by separating the SET clauses with commas. There are many other SQL keywords you can use in an action query. Probably the most important besides UPDATE is DELETE, which allows the query to delete those records that satisfy certain criteria. Finally, SQL comes with built-in functions for taking averages, finding maximums and minimums in a column, and a lot more. Consult a book on SQL for more information.
Typically, to insert values into a table, you use the INSERT statement:
INSERT INTO Books
VALUES ('Beyond HTML', '0-07-882198-3', '00788', '', 27.95)
You need a separate INSERT statement for every row being inserted in the table.
Of course, before you can query, modify, and insert data, you must have a place to store data. Use the CREATE TABLE command to make a new table. You specify the name and data type for each column. For example,
CREATE TABLE Books ( Title CHAR(60), ISBN CHAR(13), Publisher_Id CHAR(5), URL CHAR(80), Price DECIMAL(6,2) )
Table 4-5 shows the most common SQL data types.
| Data Types | Description |
|---|---|
| INTEGER or INT | Typically, a 32-bit integer |
| SMALLINT | Typically, a 16-bit integer |
| NUMERIC(m,n),DECIMAL(m,n) or DEC(m,n) | Fixed-point decimal number with m total digits and n digits after the decimal point |
| FLOAT(n) | A floating-point number with n binary digits of precision |
| REAL | Typically, a 32-bit floating-point number |
| DOUBLE | Typically, a 64-bit floating-point number |
| CHARACTER(n) or CHAR(n) | Fixed-length string of length n |
| VARCHAR(n) | Variable-length strings of maximum length n |
| BOOLEAN | A boolean value |
| DATE | Calendar date, implementation dependent |
| TIME | Time of day, implementation dependent |
| TIMESTAMP | Date and time of day, implementation dependent |
| BLOB | A binary large object |
| CLOB | A character large object |
In this book, we are not discussing the additional clauses, such as keys and constraints, that you can use with the CREATE TABLE command.
First, you need a database program that is compatible with JDBC. You will also need to create a database for your experimental use. We assume you will call this database COREJAVA. Create a new database, or have your database administrator create one with the appropriate permissions. You need to be able to create, update, and drop tables.
If you have never installed a client/server database before, you may find that setting up the database is somewhat complex and that it can be difficult to diagnose the cause for failure. It may be best to seek expert help if your setup is not working correctly.
One alternative is to install a pure Java database such as Cloudscape (http: //www.cloudscape.com), PointBase (http://www.pointbase.com), or InstantDB (http://www.lutris.com/products/instantDB). These databases are less powerful but simple to set up. The most complex part is setting up the class path, which should not pose a challenge for a seasoned Java programmer.
Essentially all database vendors already have JDBC drivers. You need to locate the vendor's instructions to load the driver into your program and to connect to the database. In the next section, we will explain the setups for two typical databases that are available on a variety of platforms:
IBM DB2
Cloudscape
Instructions for other databases are similar, although, of course, the details will vary.
We recommend against using the JDBC/ODBC bridge driver that comes with the Java 2 SDK. We recommend even more strongly against using that driver with a desktop database such as Microsoft Access. Not only are the installation and configuration somewhat cumbersome, but both the bridge driver and desktop databases have restrictions that can easily lead to confusion. Ultimately, one learns very little about real databases from this setup.
NOTE
![]() |
Some desktop databases support SQL through a proprietary mechanism, not JDBC or ODBC. For example, Borland has a BDE engine and Microsoft has a Jet engine that give somewhat better performance than ODBC for local database access. These mechanisms are not compatible with the Java platform. |
Programming with the JDBC classes is, by design, not very different from programming with the usual Java platform classes: you build objects from the JDBC core classes, extending them by inheritance if need be. This section takes you through the details.
NOTE
![]() |
The classes that you use for JDBC programming are contained in the java.sql and javax.sql packages. |
When connecting to a database, you must specify the data source and you may need to specify additional parameters. For example, network protocol drivers may need a port, and ODBC drivers may need various attributes.
As you might expect, JDBC uses a syntax similar to ordinary URLs to describe data sources. Here are examples of the syntax:
jdbc:db2:COREJAVA jdbc:cloudscape:COREJAVA;create=true
These JDBC URLs specify a DB2 or Cloudscape database named COREJAVA. The general syntax is
jdbc:subprotocol name:other stuff
where a subprotocol is used to select the specific driver for connecting to the database.
The format for the other stuff parameter depends on the subprotocol used. You need to look up your vendor's documentation for the specific format.
You need to find out the names of the JDBC driver classes used by your vendor. Typical driver names are
COM.ibm.db2.jdbc.app.DB2Driver COM.cloudscape.core.JDBCDriver
Next, you must find the library in which the driver is located, such as db2java.zip or cloudscape.jar. You must place the full path name to that driver onto the class path. Use one of the following three mechanisms:
Launch your database programs with the -classpath command-line argument.
Modify the CLASSPATH environment variable.
Copy the database library into the jre/lib/ext directory.
The DriverManager is the class responsible for selecting database drivers and creating a new database connection. However, before the driver manager can activate a driver, the driver must be registered.
There are two methods for registering drivers. The jdbc.drivers property contains a list of class names for the drivers that the driver manager will register at startup. The names are separated by colons.
You can specify the property with a command-line argument, such as
java -Djdbc.drivers=COM.ibm.db2.jdbc.app.DB2Driver MyProg
Or your application can add read a properties file with the line
jdbc.drivers=COM.ibm.db2.jdbc.app.DB2Driver
and add that setting to the system properties. (See Chapter 2 for more information on properties.)
You can also supply multiple drivers; separate them with colons, such as
COM.ibm.db2.jdbc.app.DB2Driver:COM.cloudscape.core.JDBCDriver
This approach allows users of your application to install appropriate drivers simply by modifying a property file.
Our sample programs read all database parameters from a properties file database.properties. Make sure to edit that file to match your database.
Alternatively, you can manually register a driver by loading its class.
For example,
Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");
// force registration of driver
After registering drivers, you open a database connection with code that is similar to the following example:
String url = "jdbc:db2:COREJAVA";
String username = "db2inst1";
String password = "wombat";
Connection conn = DriverManager.getConnection(url,
username, password);
The driver manager will try to find a driver that can use the protocol specified in the database URL by iterating through the available drivers currently registered with the driver manager.
For our example programs, we find it convenient to use a properties file to specify the URL, user name, and password in addition to the database driver. A typical properties file has the following contents:
jdbc.drivers=COM.ibm.db2.jdbc.app.DB2Driver jdbc.url=jdbc:db2:COREJAVA jdbc.username=db2inst1 jdbc.password=wombat
Here is the code for reading a properties file and opening the database connection.
Properties props = new Properties();
FileInputStream in
= new FileInputStream("database.properties");
props.load(in);
in.close();
String drivers = props.getProperty("jdbc.drivers");
if (drivers != null)
System.setProperty("jdbc.drivers", drivers);
String url = props.getProperty("jdbc.url");
String username = props.getProperty("jdbc.username");
String password = props.getProperty("jdbc.password");
return DriverManager.getConnection(url, username, password);
The getConnection method returns a Connection object. In the following sections, you will see how to use the connection object to execute SQL statements.
TIP
![]() |
A good way to debug JDBC-related problems is to enable JDBC tracing. Call the DriverManager.setLogWriter method to send trace messages to a PrintWriter. The trace output contains a detailed listing of the JDBC activity. |
Testing Your Database InstallationSetting up JDBC for the first time can be a bit tricky. You need several pieces of vendor-specific information, and the slightest configuration error can lead to very bewildering error messages. You should first test your database setup without JDBC. Find out how you can connect to your database and enter SQL commands. Unless a database administrator tells you to use another database, create a database called COREJAVA. Enter the following commands: CREATE TABLE Greetings (Name CHAR(20))
INSERT INTO Greetings VALUES ('Hello, World!')
SELECT * FROM Greetings
At this point, you should see a display of the "Hello, World!" entry. Now clean up: DROP TABLE Greetings Once you know that your database installation is working, and that you can log onto the database, you need to gather five pieces of information:
The first two depend on your database setup. The other three are supplied in the JDBC specific documentation from your database vendor. For example, for IBM DB2, you may have
For Cloudscape, you may have
Example 4-1 is a small test program that you can use to test your JDBC setup. Prepare the database.properties file with the information that you collected. Then start the test program with the driver library on the class path, such as java -classpath .:/usr/IBMdb2/V7.1/java/db2java.zip TestDB (Remember to use a semicolon instead of a colon as the path separator on Windows.) This program executes the same SQL instructions as the manual test. If you get a SQL error message, then you need to keep working on your setup. It is extremely common to make one or more small errors with capitalization, path names, the JDBC URL format, or the database configuration. Once the test program displays "Hello, World!", then everything is fine, and you can move on to the next section. |
1. import java.sql.*;
2. import java.io.*;
3. import java.util.*;
4.
5. /**
6. This program tests that the database and the JDBC
7. driver are correctly configured.
8. */
9. class TestDB
10. {
11. public static void main (String args[])
12. {
13. try
14. {
15. Connection conn = getConnection();
16. Statement stat = conn.createStatement();
17.
18. stat.execute("CREATE TABLE Greetings (Name CHAR(20))");
19. stat.execute(
20. "INSERT INTO Greetings VALUES ('Hello, World!')");
21.
22. ResultSet result
23. = stat.executeQuery("SELECT * FROM Greetings");
24. result.next();
25. System.out.println(result.getString(1));
26. result.close();
27.
28. stat.execute("DROP TABLE Greetings");
29.
30. stat.close();
31. conn.close();
32. }
33. catch (SQLException ex)
34. {
35. while (ex != null)
36. {
37. ex.printStackTrace();
38. ex = ex.getNextException();
39. }
40. }
41. catch (IOException ex)
42. {
43. ex.printStackTrace();
44. }
45. }
46.
47. /**
48. Gets a connection from the properties specified
49. in the file database.properties
50. @return the database connection
51. */
52. public static Connection getConnection()
53. throws SQLException, IOException
54. {
55. Properties props = new Properties();
56. FileInputStream in
57. = new FileInputStream("database.properties");
58. props.load(in);
59. in.close();
60.
61. String drivers = props.getProperty("jdbc.drivers");
62. if (drivers != null)
63. System.setProperty("jdbc.drivers", drivers);
64. String url = props.getProperty("jdbc.url");
65. String username = props.getProperty("jdbc.username");
66. String password = props.getProperty("jdbc.password");
67.
68. return
69. DriverManager.getConnection(url, username, password);
70. }
71. }
To execute a SQL command, you first create a Statement object. The Connection object that you obtained from the call to DriverManager.getConnection can be used to create statement objects.
Statement stat = conn.createStatement();
Next, you place the statement that you want to execute into a string, for example,
String command = "UPDATE Books" + " SET Price = Price - 5.00" + " WHERE Title NOT LIKE '%HTML 3%'";
Then you call the executeUpdate method of the Statement class:
stat.executeUpdate(command);
The executeUpdate method returns a count of the rows that were affected by the SQL command. For example, the call to executeUpdate in the preceding example returns the number of book records whose price was lowered by $5.00.
The executeUpdate method can execute actions such as INSERT, UPDATE, and DELETE as well as data definition commands such as CREATE TABLE, and DROP TABLE. However, you need to use the executeQuery method to execute SELECT queries. There is also a catch-all execute statement to execute arbitrary SQL statements. It's not as commonly used, except for queries that a user supplies interactively.
When you execute a query, you are interested in the result. The executeQuery object returns an object of type ResultSet that you use to walk through the result a row at a time.
ResultSet rs = stat.executeQuery("SELECT * FROM Books")
NOTE
![]() |
You can use the same Statement object for multiple, unrelated commands. However, a statement has a single open result set. If you issue multiple queries whose results you analyze concurrently, then you need multiple Statement objects. Be forewarned, though, that at least one commonly used database (Microsoft SQL Server) has a JDBC driver that allows only one active statement at a time. Use the getMaxStatements method of the DatabaseMetaData class to find out the number of concurrently open statements that your JDBC driver supports. This sounds restrictive, but in practice, you should probably not fuss with multiple concurrent result sets. If the result sets are related, then you should be able to issue a combined query and analyze a single result. It is much more efficient to let the database combine queries than it is for a Java program to iterate through multiple result sets. |
The basic loop for analyzing a result set looks like this:
while (rs.next())
{
look at a row of the result set
}
CAUTION
![]() |
The iteration protocol of the ResultSet class is slightly different from the protocol of the Iterator and Enumeration interfaces that we discussed in Chapter 2. Here, the iterator is initialized to a position before the first row. You must call the next method once to move it to the first row. |
When inspecting an individual row, you will want to know the contents of each column. A large number of accessor methods give you this information.
String isbn = rs.getString(1);
float price = rs.getDouble("Price");
There are accessors for every Java programming language type, such as getString and getDouble. Each accessor has two forms, one that takes a numeric argument and one that takes a string argument. When you supply a numeric argument, you refer to the column with that number. For example, rs.getString(1) returns the value of the first column in the current row.
CAUTION
![]() |
Unlike array indexes, database column numbers start at 1. |
When you supply a string argument, you refer to the column in the result set with that name. For example, rs.getDouble("Price") returns the value of the column with name Price. Using the numeric argument is a bit more efficient, but the string arguments make the code easier to read and maintain.
Each get method will make reasonable type conversions when the type of the method doesn't match the type of the column. For example, the call rs.getString("Price") converts the floating-point value of the Price column to a string.
NOTE
![]() |
SQL data types and Java data types are not exactly the same. See Table 4-6 for a listing of the basic SQL data types and their equivalents in the Java programming language. |
| SQL data type | Java data type |
|---|---|
| INTEGER or INT | int |
| SMALLINT | short |
| NUMERIC(m,n), DECIMAL(m,n)or DEC(m,n) | java.sql.Numeric |
| FLOAT(n) | double |
| REAL | float |
| DOUBLE | double |
| CHARACTER(n)or CHAR(n) | String |
| VARCHAR(n) | String |
| BOOLEAN | boolean |
| DATE | java.sql.Date |
| TIME | java.sql.Time |
| TIMESTAMP | java.sql.Timestamp |
| BLOB | java.sql.Blob |
| CLOB | java.sql.Clob |
| ARRAY | java.sql.Array |
In addition to numbers, strings, and dates, many databases can store large objects such as images or other data. In SQL, binary large objects are called BLOBs, and character large objects are called CLOBs. The getBlob and getClob methods return objects of type BLOB and CLOB. These classes have methods to fetch the bytes or characters in the large objects.
A SQL ARRAY is a sequence of values. For example, in a Student table, you can have a Scores column that is an ARRAY OF INTEGER. The getArray method returns an object of type java.sql.Array (which is different from the java.lang.reflect.Array class that we discussed in Volume 1). The java.sql.Array interface has methods to fetch the array values.
NOTE
![]() |
The BLOB, CLOB, and ARRAY types are features of SQL3. Java platform support for BLOB and CLOB has been greatly enhanced in JDBC 2, and support for ARRAY is a new feature in JDBC 2. |
When you get a BLOB or an array from a database, the actual contents are only fetched from the database when you request individual values. This is a useful performance enhancement, since the data can be quite voluminous.
Some databases are able to store user-defined structured types. JDBC 2 supports a mechanism for automatically mapping structured SQL types to Java objects.
However, in this introductory chapter, we do not discuss BLOBs, arrays, and user-defined types any further.
![]()
static Connection getConnection(String url, String user, String password)
establishes a connection to the given database and returns a Connection object.
| Parameters: | url | the URL for the database |
| user | the database logon ID | |
| password | the database logon password |
![]()
Statement createStatement()
creates a statement object that can be used to execute SQL queries and updates without parameters.
void close()
immediately closes the current connection.
![]()
ResultSet executeQuery(String sql)
executes the SQL statement given in the string and returns a ResultSet to view the query result.
| Parameters: | sql | the SQL query |
int executeUpdate(String sql)
executes the SQL INSERT, UPDATE, or DELETE statement specified by the string. Also used to execute Data Definition Language (DDL) statements such as CREATE TABLE. Returns the number of records affected, or -1 for a statement without an update count.
| Parameters: | sql | the SQL statement |
boolean execute(String sql)
executes the SQL statement specified by the string. Returns true if the statement returns a result set, false otherwise. Use the getResultSet or getUpdateCount method to obtain the statement outcome.
| Parameters: | sql | the SQL statement |
int getUpdateCount()
Returns the number of records affected by the preceding update statement, or -1 if the preceding statement was a statement without an update count. Call this method only once per executed statement.
ResultSet getResultSet()
Returns the result set of the preceding query statement, or null if the preceding statement did not have a result set. Call this method only once per executed statement.
![]()
boolean next()
makes the current row in the result set move forward by one. Returns false after the last row. Note that you must call this method to advance to the first row.
Xxx getXxx(int columnNumber)
Xxx getXxx(String columnName)
(Xxx is a type such as int, double, String, Date, etc.)
return the value of the column with column index columnNumber or with column names, converted to the specified type. Not all type conversions are legal. See documentation for details.
int findColumn(String columnName)
gives the column index associated with a column name.
void close()
immediately closes the current result set.
![]()
Most JDBC methods throw this exception, and you must be prepared to catch it. The following methods give more information about the exceptions.
String getSQLState()
gets the SQLState formatted, using the X/Open standard.
int getErrorCode()
gets the vendor-specific exception code.
SQLException getNextException()
gets the exception chained to this one. It may contain more information about the error.
We now want to write our first, real, JDBC program. Of course, it would be nice if we could execute some of the fancy queries that we discussed earlier. Unfortunately, we have a problem: right now, there is no data in the database. And you won't find a database file on the CD-ROM that you can simply copy onto your hard disk for the database program to read, because no database file format lets you interchange SQL relational databases from one vendor to another. SQL does not have anything to do with files. It is a language to issue queries and updates to a database. How the database execsutes these statements most efficiently and what file formats it uses toward that goal are entirely up to the implementation of the database. Database vendors try very hard to come up with clever strategies for query optimization and data storage, and different vendors arrive at different mechanisms. Thus, while SQL statements are portable, the underlying data representation is not.
To get around our problem, we provide you with a small set of data in a series of text files that contain the raw SQL instructions to create the tables and insert the values. We also give you a program that reads a file with SQL instructions, one instruction per line, and executes them.
Specifically, the program reads data from a text file in a format such as
CREATE TABLE Publisher (Publisher_Id char(5), Name char(30), URL char(80))
INSERT INTO Publisher VALUES ('01262', 'Academic Press', 'www.apnet.com')
INSERT INTO Publisher VALUES ('18835', 'Coriolis', 'www.coriolis.com/')
. . .
At the end of this section, you can see the code for the program that reads the SQL statement file and executes the statements. Even if you are not interested in looking at the implementation, you must run this program if you want to execute the more interesting examples in the remainder of this chapter. Run the program as follows:
java ExecSQL Books.sql java ExecSQL Authors.sql java ExecSQL Publishers.sql java ExecSQL BooksAuthors.sql
Before running the program, check the file database.properties. It looks like this:
jdbc.drivers=COM.cloudscape.core.JDBCDriver jdbc.url=jdbc:cloudscape:COREJAVA;create=true jdbc.username= jdbc.password=
These values work for the Cloudscape database; change them if you use another database.
CAUTION
![]() |
Make sure that both the database driver and the current directory are on the class path. Alternatively, start up the program as java -classpath driverPath:. ExecSQL SQLfile |
The following steps provide an overview of the ExecSQL program.
Connect to the database. The getConnection method reads the properties in the file database.properties and adds the jdbc.drivers property to the system properties. The driver manager uses the jdbc.drivers property to load the appropriate database driver. The getConnection method uses the jdbc.url, jdbc.username, and jdbc.password properties to open the database connection.
Open the file with the SQL commands. If no file was supplied, then prompt the user to enter the commands on the console. In that way, you can use the ExecSQL program to interactively issue simple queries.
Execute each command with the generic execute method. If it returns true, the command had a result set. The four SQL files that we provide for the book database all end in a SELECT * statement so that you can see that the data was successfully inserted.
If there was a result set, print out the result. Because this is a generic result set, we need to use meta data to find out how many columns the result has. You will learn more about meta data later in this chapter.
If there is any SQL exception, we print the exception and any chained exceptions that may be contained in it.
Example 4-2 provides the code for the program.
1. import java.io.*;
2. import java.util.*;
3. import java.sql.*;
4.
5. /**
6. Executes all SQL statements in a file.
7. Call this program as
8. java -classpath driverPath:. ExecSQL commandFile
9. */
10. class ExecSQL
11. {
12. public static void main (String args[])
13. {
14. try
15. {
16.
17. Reader reader;
18. if (args.length == 0)
19. reader = new InputStreamReader(System.in);
20. else
21. reader = new FileReader(args[0]);
22.
23. Connection conn = getConnection();
24. Statement stat = conn.createStatement();
25.
26. BufferedReader in = new BufferedReader(reader);
27.
28. boolean done = false;
29. while (!done)
30. {
31. if (args.length == 0)
32. System.out.println(
33. "Enter command or a blank line to exit:");
34.
35. String line = in.readLine();
36. if (line == null || line.length() == 0)
37. done = true;
38. else
39. {
40. try
41. {
42. boolean hasResultSet = stat.execute(line);
43. if (hasResultSet)
44. showResultSet(stat);
45. }
46. catch (SQLException ex)
47. {
48. while (ex != null)
49. {
50. ex.printStackTrace();
51. ex = ex.getNextException();
52. }
53. }
54. }
55. }
56.
57. in.close();
58. stat.close();
59. conn.close();
60. }
61. catch (Exception ex)
62. {
63. ex.printStackTrace();
64. }
65. }
66.
67. /**
68. Gets a connection from the properties specified
69. in the file database.properties
70. @return the database connection
71. */
72. public static Connection getConnection()
73. throws SQLException, IOException
74. {
75. Properties props = new Properties();
76. FileInputStream in
77. = new FileInputStream("database.properties");
78. props.load(in);
79. in.close();
80.
81. String drivers = props.getProperty("jdbc.drivers");
82. if (drivers != null)
83. System.setProperty("jdbc.drivers", drivers);
84. String url = props.getProperty("jdbc.url");
85. String username = props.getProperty("jdbc.username");
86. String password = props.getProperty("jdbc.password");
87.
88. return
89. DriverManager.getConnection(url, username, password);
90. }
91.
92. /**
93. Prints a result set.
94. @param stat the statement whose result set should be
95. printed
96. */
97. public static void showResultSet(Statement stat)
98. throws SQLException
99. {
100. ResultSet result = stat.getResultSet();
101. ResultSetMetaData metaData = result.getMetaData();
102. int columnCount = metaData.getColumnCount();
103.
104. for (int i = 1; i <= columnCount; i++)
105. {
106. if (i > 1) System.out.print(", ");
107. System.out.print(metaData.getColumnLabel(i));
108. }
109. System.out.println();
110.
111. while (result.next())
112. {
113. for (int i = 1; i <= columnCount; i++)
114. {
115. if (i > 1) System.out.print(", ");
116. System.out.print(result.getString(i));
117. }
118. System.out.println();
119. }
120. result.close();
121. }
122. }
In this section, we will write a program that executes queries against the COREJAVA database. For this program to work, you must have populated the COREJAVA database with tables, as described in the preceding section.
Figure 4-6 shows the query application.

You can select the author and the publisher or leave either of them as "Any." Click on "Query"; all books matching your selection will be displayed in the text box.
You can also change the data in the database. Select a publisher and type an amount into the text box next to the "Change prices" button. When you click on the button, all prices of that publisher are adjusted by the amount you entered, and the text area contains a message indicating how many records were changed. However, to minimize unintended changes to the database, you can't change all prices at once. The author field is ignored when you change prices. After a price change, you may want to run a query to verify the new prices.
In this program, we use one new feature, prepared statements. Consider the query for all books by a particular publisher, independent of the author. The SQL query is
SELECT Books.Price, Books.Title FROM Books, Publishers WHERE Books.Publisher_Id = Publishers.Publisher_Id AND Publishers.Name = the name from the list box
Rather than build a separate query command every time the user launches such a query, we can prepare a query with a host variable and use it many times, each time filling in a different string for the variable. That technique gives us a performance benefit. Whenever the database executes a query, it first computes a strategy of how to efficiently execute the query. By preparing the query and reusing it, you ensure that the planning step is done only once. (The reason you do not always want to prepare a query is that the optimal strategy may change as your data changes. You have to balance the expense of optimization versus the expense of querying your data less efficiently.)
Each host variable in a prepared query is indicated with a ?. If there is more than one variable, then you must keep track of the positions of the ? when setting the values. For example, our prepared query becomes
String publisherQuery = "SELECT Books.Price, Books.Title " + "FROM Books, Publishers " + "WHERE Books.Publisher_Id = Publishers.Publisher_Id " + "AND Publishers.Name = ?"; PreparedStatement publisherQueryStat = conn.prepareStatement(publisherQuery);
Before executing the prepared statement, you must bind the host variables to actual values with a set method. As with the ResultSet get methods, there are different set methods for the various types. Here, we want to set a string to a publisher name.
publisherQueryStat.setString(1, publisher);
The first argument is the host variable that we want to set. The position 1 denotes the first ?. The second argument is the value that we want to assign to the host variable.
If you reuse a prepared query that you have already executed and the query has more than one host variable, all host variables stay bound as you set them unless you change them with a set method. That means you only need to call set on those host variables that change from one query to the next.
Once all variables have been bound to values, you can execute the query
ResultSet rs = publisherQueryStat.executeQuery();
You process the result set in the usual way. Here, we add the information to the text area result.
result.setText("");
while (rs.next())
result.appendText(rs.getString(1) + ", " +
rs.getString(2y) + "\n");
rs.close();
There are a total of four prepared queries in this program, one each for the cases shown in Table 4-7.
| Author | Publisher |
|---|---|
| any | any |
| any | specified |
| specified | any |
| specified | specified |
The price update feature is implemented as a simple UPDATE statement. For variety, we did not choose to make a prepared statement in this case. Note that we call executeUpdate, not executeQuery, since the UPDATE statement does not return a result set and we don't need one. The return value of executeUpdate is the count of changed rows. We display the count in the text area.
String updateStatement = "UPDATE Books ..."; int r = stat.executeUpdate(updateStatement); result.setText(r + " records updated");
The following steps provide an overview of the program.
Arrange the components in the frame, using a grid bag layout (see Chapter 9 in Volume 1).
Populate the author and publisher text boxes by running two queries that return all author and publisher names in the database.
When the user selects "Query," find which of the four query types needs to be executed. If this is the first time this query type is executed, then the prepared statement variable is null, and the prepared statement is constructed. Then, the values are bound to the query and the query is executed.
The queries involving authors are more complex. Because a book can have multiple authors, the BooksAuthors table gives the correspondence between authors and books. For example, the book with ISBN number 1-56-604288-7 has two authors with codes HARR and KIDD. The BooksAuthors table has the rows
1-56-604288-7, HARR, 1 1-56-604288-7, KIDD, 2
to indicate this fact. The third column lists the order of the authors. (We can't just use the position of the records in the table. There is no fixed row ordering in a relational table.) Thus, the query has to snake (join) itself from the Books table to the BooksAuthors table, then to the Authors table to compare the author name with the one selected by the user.
SELECT Books.Price, Books.Title FROM Books, Publishers, BooksAuthors, Authors WHERE Books.Publisher_Id = Publishers.Publisher_Id AND Publishers.Name = ? AND Books.ISBN = BooksAuthors.ISBN AND BooksAuthors.Author = Authors.Author AND Authors.Name = ?
TIP
![]() |
Some Java programmers avoid complex SQL statements such as this one. A surprisingly common, but very inefficient, workaround is to write lots of Java code that iterates through multiple result sets. But the database is a lot better at executing query code than a Java program—that's the core competency of a database. As a rule of thumb: If you can do it in SQL, don't do it in Java. |
The results of the query are displayed in the results text box.
When the user selects "Change prices," then the update query is constructed and executed. The query is quite complex because the WHERE clause of the UPDATE statement needs the publisher code and we know only the publisher name. This problem is solved with a nested subquery.
UPDATE Books SET Price = Price + price change WHERE Books.Publisher_Id = (SELECT Publisher_Id FROM Publishers WHERE Name = publisher name)
We initialize the connection and statement objects in the constructor. We hang on to them for the life of the program. Just before the program exits, we trap the "window closing" event, and these objects are closed.
class QueryDB extends Frame
{
QueryDB()
{
conn = getConnection();
stat = conn.createStatement();
. . .
add(new
WindowAdapter()
{
public void windowClosing(WindowEvent event)
{
try
{
stat.close();
conn.close();
}
catch(SQLException ex)
{
while (ex != null)
{
ex.printStackTrace();
ex = ex.getNextException();
}
}
}
});
}
. . .
Connection conn;
Statement stat;
}
Example 4-3 is the complete program code.
1. import java.net.*;
2. import java.sql.*;
3. import java.awt.*;
4. import java.awt.event.*;
5. import java.io.*;
6. import java.util.*;
7. import javax.swing.*;
8.
9. /**
10. This program demonstrates several complex database queries.
11. */
12. public class QueryDB
13. {
14. public static void main(String[] args)
15. {
16. JFrame frame = new QueryDBFrame();
17. frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
18. frame.show();
19. }
20. }
21.
22. /**
23. This frame displays combo boxes for query parameters,
24. a text area for command results, and buttons to launch
25. a query and an update.
26. */
27. class QueryDBFrame extends JFrame
28. {
29. public QueryDBFrame()
30. {
31. setTitle("QueryDB");
32. setSize(WIDTH, HEIGHT);
33. getContentPane().setLayout(new GridBagLayout());
34. GridBagConstraints gbc = new GridBagConstraints();
35.
36. authors = new JComboBox();
37. authors.setEditable(false);
38. authors.addItem("Any");
39.
40. publishers = new JComboBox();
41. publishers.setEditable(false);
42. publishers.addItem("Any");
43.
44. result = new JTextArea(4, 50);
45. result.setEditable(false);
46.
47. priceChange = new JTextField(8);
48. priceChange.setText("-5.00");
49.
50. try
51. {
52. conn = getConnection();
53. stat = conn.createStatement();
54.
55. String query = "SELECT Name FROM Authors";
56. ResultSet rs = stat.executeQuery(query);
57. while (rs.next())
58. authors.addItem(rs.getString(1));
59. rs.close();
60.
61. query = "SELECT Name FROM Publishers";
62. rs = stat.executeQuery(query);
63. while (rs.next())
64. publishers.addItem(rs.getString(1));
65. rs.close();
66. }
67. catch(SQLException ex)
68. {
69. result.setText("");
70. while (ex != null)
71. {
72. result.append("" + ex);
73. ex = ex.getNextException();
74. }
75. }
76. catch (IOException ex)
77. {
78. result.setText("" + ex);
79. }
80.
81. gbc.fill = GridBagConstraints.NONE;
82. gbc.weightx = 100;
83. gbc.weighty = 100;
84. add(authors, gbc, 0, 0, 2, 1);
85.
86. add(publishers, gbc, 2, 0, 2, 1);
87.
88. gbc.fill = GridBagConstraints.NONE;
89. JButton queryButton = new JButton("Query");
90. queryButton.addActionListener(new
91. ActionListener()
92. {
93. public void actionPerformed(ActionEvent event)
94. {
95. executeQuery();
96. }
97. });
98. add(queryButton, gbc, 0, 1, 1, 1);
99.
100. JButton changeButton = new JButton("Change prices");
101. changeButton.addActionListener(new
102. ActionListener()
103. {
104. public void actionPerformed(ActionEvent event)
105. {
106. changePrices();
107. }
108. });
109. add(changeButton, gbc, 2, 1, 1, 1);
110.
111. gbc.fill = GridBagConstraints.HORIZONTAL;
112. add(priceChange, gbc, 3, 1, 1, 1);
113.
114. gbc.fill = GridBagConstraints.BOTH;
115. add(new JScrollPane(result), gbc, 0, 2, 4, 1);
116.
117. add(new
118. WindowAdapter()
119. {
120. public void windowClosing(WindowEvent event)
121. {
122. try
123. {
124. stat.close();
125. conn.close();
126. }
127. catch(SQLException ex)
128. {
129. while (ex != null)
130. {
131. ex.printStackTrace();
132. ex = ex.getNextException();
133. }
134. }
135. }
136. });
137. }
138.
139. /**
140. Add a component to this frame.
141. @param c the component to add
142. @param gbc the grid bag constraints
143. @param x the grid bax column
144. @param y the grid bag row
145. @param w the number of grid bag columns spanned
146. @param h the number of grid bag rows spanned
147. */
148. private void add(Component c, GridBagConstraints gbc,
149. int x, int y, int w, int h)
150. {
151. gbc.gridx = x;
152. gbc.gridy = y;
153. gbc.gridwidth = w;
154. gbc.gridheight = h;
155. getContentPane().add(c, gbc);
156. }
157.
158. /**
159. Executes the selected query.
160. */
161. private void executeQuery()
162. {
163. ResultSet rs = null;
164. try
165. {
166. String author
167. = (String)authors.getSelectedItem();
168. String publisher
169. = (String)publishers.getSelectedItem();
170. if (!author.equals("Any")
171. && !publisher.equals("Any"))
172. {
173. if (authorPublisherQueryStmt == null)
174. {
175. String authorPublisherQuery =
176. "SELECT Books.Price, Books.Title " +
177. "FROM Books, BooksAuthors, Authors, Publishers " +
178. "WHERE Authors.Author_Id = BooksAuthors.Author_Id AND " +
179. "BooksAuthors.ISBN = Books.ISBN AND " +
180. "Books.Publisher_Id = Publishers.Publisher_Id AND " +
181. "Authors.Name = ? AND " +
182. "Publishers.Name = ?";
183. authorPublisherQueryStmt
184. = conn.prepareStatement(authorPublisherQuery);
185. }
186. authorPublisherQueryStmt.setString(1, author);
187. authorPublisherQueryStmt.setString(2,
188. publisher);
189. rs = authorPublisherQueryStmt.executeQuery();
190. }
191. else if (!author.equals("Any")
192. && publisher.equals("Any"))
193. {
194. if (authorQueryStmt == null)
195. {
196. String authorQuery =
197. "SELECT Books.Price, Books.Title " +
198. "FROM Books, BooksAuthors, Authors " +
199. "WHERE Authors.Author_Id = BooksAuthors.Author_Id AND " +
200. "BooksAuthors.ISBN = Books.ISBN AND " +
201. "Authors.Name = ?";
202. authorQueryStmt
203. = conn.prepareStatement(authorQuery);
204. }
205. authorQueryStmt.setString(1, author);
206. rs = authorQueryStmt.executeQuery();
207. }
208. else if (author.equals("Any")
209. && !publisher.equals("Any"))
210. {
211. if (publisherQueryStmt == null)
212. {
213. String publisherQuery =
214. "SELECT Books.Price, Books.Title " +
215. "FROM Books, Publishers " +
216. "WHERE Books.Publisher_Id = Publishers.Publisher_Id AND " +
217. "Publishers.Name = ?";
218. publisherQueryStmt
219. = conn.prepareStatement(publisherQuery);
220. }
221. publisherQueryStmt.setString(1, publisher);
222. rs = publisherQueryStmt.executeQuery();
223. }
224. else
225. {
226. if (allQueryStmt == null)
227. {
228. String allQuery =
229. "SELECT Books.Price, Books.Title FROM Books";
230. allQueryStmt
231. = conn.prepareStatement(allQuery);
232. }
233. rs = allQueryStmt.executeQuery();
234. }
235.
236. result.setText("");
237. while (rs.next())
238. {
239. result.append(rs.getString(1));
240. result.append(", ");
241. result.append(rs.getString(2));
242. result.append("\n");
243. }
244. rs.close();
245. }
246. catch(SQLException ex)
247. {
248. result.setText("");
249. while (ex != null)
250. {
251. result.append("" + ex);
252. ex = ex.getNextException();
253. }
254. }
255. }
256.
257. /**
258. Executes an update statement to change prices.
259. */
260. public void changePrices()
261. {
262. String publisher
263. = (String)publishers.getSelectedItem();
264. if (publisher.equals("Any"))
265. {
266. result.setText
267. ("I am sorry, but I cannot do that.");
268. return;
269. }
270. try
271. {
272. String updateStatement =
273. "UPDATE Books " +
274. "SET Price = Price + " + priceChange.getText() +
275. " WHERE Books.Publisher_Id = " +
276. "(SELECT Publisher_Id FROM Publishers WHERE Name = '" +
277. publisher + "')";
278. int r = stat.executeUpdate(updateStatement);
279. result.setText(r + " records updated.");
280. }
281. catch(SQLException ex)
282. {
283. result.setText("");
284. while (ex != null)
285. {
286. result.append("" + ex);
287. ex = ex.getNextException();
288. }
289. }
290. }
291.
292. /**
293. Gets a connection from the properties specified
294. in the file database.properties
295. @return the database connection
296. */
297. public static Connection getConnection()
298. throws SQLException, IOException
299. {
300. Properties props = new Properties();
301. FileInputStream in
302. = new FileInputStream("database.properties");
303. props.load(in);
304. in.close();
305.
306. String drivers = props.getProperty("jdbc.drivers");
307. if (drivers != null)
308. System.setProperty("jdbc.drivers", drivers);
309. String url = props.getProperty("jdbc.url");
310. String username = props.getProperty("jdbc.username");
311. String password = props.getProperty("jdbc.password");
312.
313. return
314. DriverManager.getConnection(url, username, password);
315. }
316.
317. public static final int WIDTH = 400;
318. public static final int HEIGHT = 400;
319.
320. private JComboBox authors;
321. private JComboBox publishers;
322. private JTextField priceChange;
323. private JTextArea result;
324. private Connection conn;
325. private Statement stat;
326. private PreparedStatement authorQueryStmt;
327. private PreparedStatement authorPublisherQueryStmt;
328. private PreparedStatement publisherQueryStmt;
329. private PreparedStatement allQueryStmt;
330. }
![]()
PreparedStatement prepareStatement(String sql)
returns a PreparedStatement object containing the precompiled statement. The string sql contains a SQL statement that can contain one or more parameter placeholders denoted by ? characters.
![]()
void setXxx(int n, Xxx x)
(Xxx is a type such as int, double, String, Date, etc.)
sets the value of the nth parameter to x.
void clearParameters()
clears all current parameters in the prepared statement.
ResultSet executeQuery()
executes a prepared SQL query and returns a ResultSet object.
int executeUpdate()
executes the prepared SQL INSERT, UPDATE, or DELETE statement represented by the PreparedStatement object. Returns the number of rows affected, or 0 for DDL statements.
The most useful improvements in JDBC 2 are in the ResultSet class. As you have seen, the next method of the ResultSet class iterates over the rows in a result set. That is certainly adequate for a program that needs to analyze the data. However, consider a visual data display that shows a table or query result (see Figure 4-7). You usually want the user to be able to move both forward and backward in the result set. But in JDBC 1, there was no previous method. Programmers who wanted to implement backwards iteration had to manually cache the result set data. The scrolling result set in JDBC 2 lets you move forward and backward through a result set and jump to any position in the result set.

Furthermore, once you display the contents of a result set to users, they may be tempted to edit it. If you supply an editable view to your users, you have to make sure that the user edits are posted back to the database. In JDBC 1, you had to program UPDATE statements. In JDBC 2, you can simply update the result set entries, and the database is automatically updated.
JDBC 2 delivers additional enhancements to result sets, such as the capability of updating a result set with the most recent data if the data has been modified by another concurrent database connection. JDBC 3 adds another refinement, specifying the behavior of result sets when a transaction is committed. One form of the getStatement method of the Connection class lets you specify the holdability of the result sets. You can choose to have result sets automatically close when a transaction is committed, or to keep them open. However, these advanced features are outside the scope of this introductory chapter. We refer you to the JDBC API Tutorial and Reference by Seth White, et al. [Addison-Wesley 1999], and the JDBC specification documents at http://java.sun.com/products/jdbc for more information.
To obtain scrolling result sets from your queries, you must obtain a different Statement object with the method
Statement stat = conn.createStatement(type, concurrency);
For a prepared statement, use the call
PreparedStatement stat = conn.prepareStatement(command, type, concurrency);
The possible values of type and concurrency are listed in Table 4-8 and Table 4-9. You have the following choices:
Do you want the result set to be scrollable or not? If not, use ResultSet.TYPE_FORWARD_ONLY.
If the result set is scrollable, do you want it to be able to reflect changes in the database that occurred after the query that yielded it? (In our discussion, we will assume the ResultSet.TYPE_SCROLL_INSENSITIVE setting for scrolling result sets. This assumes that the result set does not "sense" database changes that occurred after the query.)
Do you want to be able to update the database by editing the result set? (See the next section for details.)
For example, if you simply want to be able to scroll through a result set but you don't want to edit its data, you use:
Statement stat
= conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
| TYPE_FORWARD_ONLY | The result set is not scrollable. |
| TYPE_SCROLL_INSENSITIVE | The result set is scrollable but not sensitive to database changes. |
| TYPE_SCROLL_SENSITIVE | The result set is scrollable and sensitive to database changes. |
| CONCUR_READ_ONLY | The result set cannot be used to update the database. |
| CONCUR_UPDATABLE | The result set can be used to update the database. |
All result sets that are returned by method calls
ResultSet rs = stat.executeQuery(query)
are now scrollable. A scrolling result set has a cursor that indicates the current position.
NOTE
![]() |
Actually, a database driver might not be able to honor your request for a scrolling or updatable cursor. (The supportsResultSetType and supportsResultSetConcurrency methods of the DatabaseMetaData class tell you which types and concurrency modes are supported by a particular database.) But even if a database supports all result set modes, a particular query might not be able to yield a result set with all the properties that you requested. (For example, the result set of a complex query may not be updatable.) In that case, the executeQuery method returns a ResultSet of lesser capabilities and adds a warning to the connection object. You can retrieve warnings with the getWarnings method of the Connection class. Alternatively, you can use the getType and getConcurrency methods of the ResultSet class to find out what mode a result set actually has. If you do not check the result set capabilities and issue an unsupported operation, such as previous on a result set that is not scrollable, then the operation throws a SQLException. |
CAUTION
![]() |
In JDBC 1 drivers, the Connection class does not have a method Statement createStatement(int type, int concurrency); If a program that you compiled for JDBC 2 inadvertently loads a JDBC 1 driver and then calls this nonexistent method, the program will crash. Unfortunately, there is no JDBC 2 mechanism for querying a driver as to whether it is JDBC 2 compliant. In JDBC 3, you can use the getJDBCMajorVersion and getJDBCMinorVersion methods of the DatabaseMetaData class to find the JDBC version number of the driver. |
Scrolling is very simple. You use
if (rs.previous()) . . .
to scroll backward. The method returns true if the cursor is positioned on an actual row; false if it now is positioned before the first row.
You can move the cursor backward or forward by a number of rows with the command
rs.relative(n);
If n is positive, the cursor moves forward. If n is negative, it moves backwards. If n is zero, the call has no effect. If you attempt to move the cursor outside the current set of rows, it is set to point either after the last row or before the first row, depending on the sign of n. Then, the method returns false and the cursor does not move. The method returns true if the cursor landed on an actual row.
Alternatively, you can set the cursor to a particular row number:
rs.absolute(n);
You get the current row number with the call
int n = rs.getRow();
The first row in the result set has number 1. If the return value is 0, the cursor is not currently on a row—it is either before the first or after the last row.
NOTE
![]() |
The order of the rows in a result set is completely arbitrary. You should never attach any significance to the row numbers. |
There are convenience methods
first last beforeFirst afterLast
to move the cursor to the first, to the last, before the first, or after the last position.
Finally, the methods
isFirst isLast isBeforeFirst isAfterLast
test whether the cursor is at one of these special positions.
Using a scrollable result set is very simple. The hard work of caching the query data is carried out behind the scenes by the database driver.
TIP
![]() |
If you want to implement a scrollable view in a web application, then the scrollable result set has a major drawback. It requires you to keep the connection to the database open between page requests. However, users can walk away from their computer for a long time, or simply abandon your site, leaving the connection occupied. That is not good—database connections are scarce resources. In such a situation, use a row set. The RowSet interface extends the ResultSet interface, but row sets aren't tied to a database connection. They can be cached, or they can present data from a source other than a database. The article http://developer.java.sun.com/developer/technicalArticles/javaserverpages/cachedrowset shows how to use row sets in a web application. |
If you want to be able to edit result set data and have the changes automatically reflected in the database, you need to create an updatable result set. Updatable result sets don't have to be scrollable, but if you present data to a user for editing, you usually want to allow scrolling as well.
To obtain updatable result sets, you create a statement as follows.
Statement stat
= conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
Then, the result sets returned by a call to executeQuery are updatable.
NOTE
![]() |
Not all queries return updatable result sets. If your query is a join that involves multiple tables, the result may not be updatable. If your query involves only a single table or if it joins multiple tables by their primary keys, you should expect the result set to be updatable. Call the getConcurrency method of the ResultSet class to find out for sure. |
For example, suppose you want to raise the prices of some books, but you don't have a simple criterion for issuing an UPDATE command. Then, you can iterate through all books and update prices, based on arbitrary conditions.
String query = "SELECT * FROM Books";
ResultSet rs = stat.executeQuery(query);
while (rs.next())
{
if (. . .)
{
double increase = . . .
double price = rs.getDouble("Price");
rs.updateDouble("Price", price + increase);
rs.updateRow();
}
}
There are updateXxx methods for all data types that correspond to SQL types, such as updateDouble, updateString, and so on. As with the getXxx methods, you specify the name or the number of the column. Then, you specify the new value for the field.
NOTE
![]() |
If you use the updateXxx method whose first parameter is the column number, be aware that this is the column number in the result set. It may well be different from the column number in the database. |
The updateXxx method only changes the row values, not the database. When you are done with the field updates in a row, you must call the updateRow method. That method sends all updates in the current row to the database. If you move the cursor to another row without calling updateRow, all updates are discarded from the row set and they are never communicated to the database. You can also call the cancelRowUpdates method to cancel the updates to the current row.
The preceding example shows how you modify an existing row. If you want to add a new row to the database, you first use the moveToInsertRow method to move the cursor to a special position, called the insert row. You build up a new row in the insert row position by issuing updateXxx instructions. Finally, when you are done, call the insertRow method to deliver the new row to the database. When you are done inserting, call moveToCurrentRow to move the cursor back to the position before the call to moveToInsertRow. Here is an example.
rs.moveToInsertRow();
rs.updateString("Title", title);
rs.updateString("ISBN", isbn);
rs.updateString("Publisher_Id", pubid);
rs.updateString("URL", url);
rs.updateDouble("Price", price);
rs.insertRow();
rs.moveToCurrentRow();
Note that you have no influence where the new data is added in the result set or the database.
Finally, you can delete the row under the cursor.
rs.deleteRow();
The deleteRow method immediately removes the row from both the result set and the database.
The updateRow, insertRow, and deleteRow methods of the ResultSet class give you the same power as executing UPDATE, INSERT, and DELETE SQL commands. However, programmers who are used to the Java programming language will find it more natural to manipulate the database contents through result sets than by constructing SQL statements. Once JDBC 2 drivers are widely available, this programming style is likely to become quite popular.
CAUTION
![]() |
If you are not careful, you can write staggeringly inefficient code with updatable result sets. It is much more efficient to execute an UPDATE statement than it is to make a query and iterate through the result, changing data along the way. Updatable result sets make sense for interactive programs in which a user can make arbitrary changes, but for most programmatic changes, a SQL UPDATE is more appropriate. |
![]()
Statement createStatement(int type, int concurrency)
PreparedStatement prepareStatement(String command, int type, int concurrency)
(JDBC 2) create a statement or prepared statement that yields result sets with the given type and concurrency.
| Parameters: | command | the command to prepare |
| type | one of the constants TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE, or TYPE_SCROLL_SENSITIVE of the ResultSet interface | |
| concurrency | one of the constants CONCUR_READ_ONLY or CONCUR_UPDATABLE of the ResultSet interface |
SQLWarning getWarnings()
returns the first of the pending warnings on this connection, or null if no warnings are pending. The warnings are chained together—keep calling getNextWarning on the returned SQLWarning object until that method returns null. This call does not consume the warnings. The SQLWarning class extends SQLException. Use the inherited getErrorCode and getSQLState to analyze the warnings.
void clearWarnings()
clears all warnings that have been reported on this connection.
![]()
int getType()
(JDBC 2) returns the type of this result set, one of TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE, or TYPE_SCROLL_SENSITIVE.
int getConcurrency()
(JDBC 2) returns the concurrency setting of this result set, one of CONCUR_READ_ONLY or CONCUR_UPDATABLE.
boolean previous()
(JDBC 2) moves the cursor to the preceding row. Returns true if the cursor is positioned on a row.
int getRow()
(JDBC 2) gets the number of the current row. Rows are numbered starting with 1.
boolean absolute(int r)
(JDBC 2) moves the cursor to row r. Returns true if the cursor is positioned on a row.
boolean relative(int d)
(JDBC 2) moves the cursor by d rows. If d is negative, the cursor is moved backward. Returns true if the cursor is positioned on a row.
boolean first()
boolean last()
(JDBC 2) move the cursor to the first or last row. Return true if the cursor is positioned on a row.
void beforeFirst()
void afterLast()
(JDBC 2) move the cursor before the first or after the last row.
boolean isFirst()
boolean isLast()
(JDBC 2) test if the cursor is at the first or last row.
boolean isBeforeFirst()
boolean isAfterLast()
(JDBC 2) test if the cursor is before the first or after the last row.
void moveToInsertRow()
(JDBC 2) moves the cursor to the insert row. The insert row is a special row that is used for inserting new data with the updateXxx and insertRow methods.
void moveToCurrentRow()
(JDBC 2) moves the cursor back from the insert row to the row that it occupied when the moveToInsertRow method was called.
void insertRow()
(JDBC 2) inserts the contents of the insert row into the database and the result set.
void deleteRow()
(JDBC 2) deletes the current row from the database and the result set.
void updateXxx(int column, Xxx data)
void updateXxx(String columnName, Xxx data)
(Xxx is a type such as int, double, String, Date, etc.)
(JDBC 2) update a field in the current row of the result set.
void updateRow()
(JDBC 2) sends the current row updates to the database.
void cancelRowUpdates()
(JDBC 2) cancels the current row updates.
![]()
boolean supportsResultSetType(int type)
(JDBC 2) returns true if the database can support result sets of the given type.
| Parameters: | command | the command to prepare |
| type | one of the constants TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE, or TYPE_SCROLL_SENSITIVE of the ResultSet interface |
boolean supportsResultSetConcurrency(int type, int concurrency)
(JDBC 2) Returns true if the database can support result sets of the given combination of type and concurrency.
| Parameters: | command | the command to prepare |
| type | one of the constants TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE, or TYPE_SCROLL_SENSITIVE of the ResultSet interface | |
| concurrency | one of the constants CONCUR_READ_ONLY or CONCUR_UPDATABLE of the ResultSet interface |
In the preceding sections, you saw how to populate, query, and update database tables. However, JDBC can give you additional information about the structure of a database and its tables. For example, you can get a list of the tables in a particular database or the column names and types of a table. This information is not useful when you are implementing a particular database application. After all, if you design the tables, you know the tables and their structure. Structural information is, however, extremely useful for programmers who write tools that work with any database.
In this section, we will show you how to write such a simple tool. This tool lets you browse all tables in a database.
The combo box on top displays all tables in the database. Select one of them, and the center of the frame is filled with the field names of that table and the values of the first record, as shown in Figure 4-8. Click on "Next" to scroll through the records in the table.

We fully expect tool vendors to develop much more sophisticated versions of programs like this one. For example, it would be possible to let the user edit the values or add new ones, and then update the database. We developed this program mostly to show you how such tools can be built.
In SQL, data that describes the database or one of its parts is called metadata (to distinguish it from the actual data that is stored in the database). You can get two kinds of metadata: about a database and about a result set.
To find out more about the database, you need to request an object of type DatabaseMetaData from the database connection.
DatabaseMetaData meta = conn.getMetaData();
Now you are ready to get some metadata. For example, the call
ResultSet rs = meta.getTables(null, null, null, new String[]
{ "TABLE" });
returns a result set that contains information about all tables in the database. (See the API note for other parameters to this method.)
Each row in the result set contains information about the table. We only care about the third entry, the name of the table. (Again, see the API note for the other columns.) Thus, rs.getString(3) is the table name. Here is the code that populates the combo box.
while (rs.next()) tableNames.addItem(rs.getString(3)); rs.close();
The DatabaseMetaData class gives data about the database. There is a second metadata class, ResultSetMetaData, that reports information about a result set. Whenever you have a result set from a query, you can inquire about the number of columns and each column's name, type, and field width.
We will make use of this information to make a label for each name and a text field of sufficient size for each value.
ResultSet rs = stat.executeQuery("SELECT * FROM " + tableName);
ResultSetMetaData meta = rs.getMetaData();
for (int i = 1; i <= meta.getColumnCount(); i++)
{
String columnName = meta.getColumnLabel(i);
int columnWidth = meta.getColumnDisplaySize(i);
Label l = new Label(columnName);
TextField tf = new TextField(columnWidth);
. . .
}
There is a second important use for database meta data. Databases are complex, and the SQL standard leaves plenty of room for variability. There are well over a hundred methods in the DatabaseMetaData class to inquire about the database, including calls with exotic names such as
md.supportsCatalogsInPrivilegeDefinitions()
and
md.nullPlusNonNullIsNull()
Clearly, these are geared toward advanced users with special needs, in particular, those who need to write highly portable code that works with multiple databases. In our sample program, we only give one example of this technique. We ask the database metadata whether the JDBC driver supports scrolling result sets. If so, we open a scrolling result set and add a "Previous" button for scrolling backwards.
if (meta.supportsResultSetType( ResultSet.TYPE_SCROLL_INSENSITIVE)) . . .
The following steps provide a brief overview of the sample program.
Have the border layout put the table name combo box on the top and the table values in the center.
Connect to the database. Find out if it supports scrolling result sets. If so, create the Statement object to yield scrolling result sets. Otherwise, just create a default Statement.
Get the table names and fill them into the choice component.
If scrolling is supported, add the "Previous" button. Always add the "Next" button.
When the user selects a table, make a query to see all its values. Get the result set metadata. Throw out the old scroll pane from the center panel. Create a panel containing a gridbag layout of labels and text fields. Add it to the frame and call the validate method to recompute the frame layout. Then, call showNextRow to show the first row.
The showNextRow method is called to show the first record and is also called whenever the "Next" button is clicked. It gets the next row from the table and fills the column values into the text boxes.
There is a slight subtlety in detecting the end of the result set. When the result set is scrolling, we can simply use the isLast method. But when it isn't scrolling, that method call will cause an exception (or even a JVM error if the driver is a JDBC 1 driver). Therefore, we use a different strategy for non-scrolling result sets. When rs.next() returns false, we close the result set and set rs to null.
The "Previous" button calls showPreviousRow, which moves the result set backwards. Since this button is only installed when the result set is scrollable, we know that the previous and isFirst method are supported.
The showRow method simply fills in all the result set fields into the text fields of the data panel.
Example 4-4 is the program.
1. import java.net.*;
2. import java.sql.*;
3. import java.awt.*;
4. import java.awt.event.*;
5. import java.io.*;
6. import java.util.*;
7. import javax.swing.*;
8.
9. /**
10. This program uses metadata to display arbitrary tables
11. in a database.
12. */
13. public class ViewDB
14. {
15. public static void main(String[] args)
16 {
17. JFrame frame = new ViewDBFrame();
18. frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
19. frame.show();
20. }
21. }
22.
23. /**
24. The frame that holds the data panel and the navigation
25. buttons.
26. */
27. class ViewDBFrame extends JFrame
28. {
29. public ViewDBFrame()
30. {
31. setTitle("ViewDB");
32. setSize(WIDTH, HEIGHT);
33.
34. Container contentPane = getContentPane();
35.
36. tableNames = new JComboBox();
37. tableNames.addActionListener(new
38. ActionListener()
39. {
40. public void actionPerformed(ActionEvent event)
41. {
42. showTable((String)tableNames.getSelectedItem());
43. }
44. });
45. contentPane.add(tableNames, BorderLayout.NORTH);
46.
47. try
48. {
49. conn = getConnection();
50. meta = conn.getMetaData();
51. createStatement();
52. getTableNames();
53. }
54. catch(Exception ex)
55. {
56. JOptionPane.showMessageDialog(this, ex);
57. }
58.
59. JPanel buttonPanel = new JPanel();
60. contentPane.add(buttonPanel, BorderLayout.SOUTH);
61.
62. if (scrolling)
63. {
64. previousButton = new JButton("Previous");
65. previousButton.addActionListener(new
66. ActionListener()
67. {
68. public void actionPerformed(ActionEvent event)
69. {
70. showPreviousRow();
71. }
72. });
73. buttonPanel.add(previousButton);
74. }
75.
76. nextButton = new JButton("Next");
77. nextButton.addActionListener(new
78. ActionListener()
79. {
80. public void actionPerformed(ActionEvent event)
81. {
82. showNextRow();
83. }
84. });
85. buttonPanel.add(nextButton);
86.
87. add(new
88. WindowAdapter()
89. {
90. public void windowClosing(WindowEvent event)
91. {
92. try
93. {
94. stat.close();
95. conn.close();
96. }
97. catch(SQLException ex)
98. {
99. while (ex != null)
100. {
101. ex.printStackTrace();
102. ex = ex.getNextException();
103. }
104. }
105. }
106. });
107. }
108. }
109.
110. /**
111. Creates the statement object used for executing queries.
112. If the database supports scrolling cursors, the statement
113. is created to yield them.
114. */
115. public void createStatement() throws SQLException
116. {
117. if (meta.supportsResultSetType(
118. ResultSet.TYPE_SCROLL_INSENSITIVE))
119. {
120. stat = conn.createStatement(
121. ResultSet.TYPE_SCROLL_INSENSITIVE,
122. ResultSet.CONCUR_READ_ONLY);
123. scrolling = true;
124. }
125. else
126. {
127. stat = conn.createStatement();
128. scrolling = false;
129. }
130. }
131.
132. /**
133. Gets all table names of this database and adds them
134. to the combo box.
135. */
136. public void getTableNames() throws SQLException
137. {
138. ResultSet mrs = meta.getTables(null, null, null,
139. new String[] { "TABLE" });
140. while (mrs.next())
141. tableNames.addItem(mrs.getString(3));
142. mrs.close();
143. }
144.
145. /**
146. Prepares the text fields for showing a new table, and
147. shows the first row.
148. @param tableName the name of the table to display
149. */
150. public void showTable(String tableName)
151. {
152. try
153. {
154. if (rs != null) rs.close();
155. rs = stat.executeQuery("SELECT * FROM " + tableName);
156. if (scrollPane != null)
157. getContentPane().remove(scrollPane);
158. dataPanel = new DataPanel(rs);
159. scrollPane = new JScrollPane(dataPanel);
160. getContentPane().add(scrollPane, BorderLayout.CENTER);
161. validate();
162. showNextRow();
163. }
164. catch(Exception ex)
165. {
166. JOptionPane.showMessageDialog(this, ex);
167. }
168. }
169.
170. /**
171. Moves to the previous table row.
172. */
173. public void showPreviousRow()
174. {
175. try
176. {
177. if (rs == null || rs.isFirst()) return;
178. rs.previous();
179. dataPanel.showRow(rs);
180. }
181. catch(Exception ex)
182. {
183. System.out.println("Error " + ex);
184. }
185. }
186.
187. /**
188. Moves to the next table row.
189. */
190. public void showNextRow()
191. {
192. try
193. {
194. if (rs == null || scrolling && rs.isLast()) return;
195.
196. if (!rs.next() && !scrolling)
197. {
198. rs.close();
199. rs = null;
200. return;
201. }
202.
203. dataPanel.showRow(rs);
204. }
205. catch(Exception ex)
206. {
207. System.out.println("Error " + ex);
208. }
209. }
210.
211. /**
212. Gets a connection from the properties specified
213. in the file database.properties
214. @return the database connection
215. */
216. public static Connection getConnection()
217. throws SQLException, IOException
218. {
219. Properties props = new Properties();
220. FileInputStream in
221. = new FileInputStream("database.properties");
222. props.load(in);
223. in.close();
224.
225. String drivers = props.getProperty("jdbc.drivers");
226. if (drivers != null)
227. System.setProperty("jdbc.drivers", drivers);
228. String url = props.getProperty("jdbc.url");
229. String username = props.getProperty("jdbc.username");
230. String password = props.getProperty("jdbc.password");
231.
232. return
233. DriverManager.getConnection(url, username, password);
234. }
235.
236. public static final int WIDTH = 300;
237. public static final int HEIGHT = 200;
238.
239. private JButton previousButton;
240. private JButton nextButton;
241. private DataPanel dataPanel;
242. private JScrollPane scrollPane;
243. private JComboBox tableNames;
244.
245. private Connection conn;
246. private Statement stat;
247. private DatabaseMetaData meta;
248. private ResultSet rs;
249. private boolean scrolling;
250. }
251.
252. /**
253. This panel displays the contents of a result set.
254. */
255. class DataPanel extends JPanel
256. {
257. /**
258. Constructs the data panel.
259. @param rs the result set whose contents this panel displays
260. */
261. public DataPanel(ResultSet rs) throws SQLException
262. {
263. fields = new ArrayList();
264. setLayout(new GridBagLayout());
265. GridBagConstraints gbc = new GridBagConstraints();
266. gbc.weighty = 100;
267.
268. ResultSetMetaData rsmd = rs.getMetaData();
269. for (int i = 1; i <= rsmd.getColumnCount(); i++)
270. {
271. String columnName = rsmd.getColumnLabel(i);
272. int columnWidth = rsmd.getColumnDisplaySize(i);
273. JTextField tb = new JTextField(columnWidth);
274. fields.add(tb);
275.
276. gbc.weightx = 0;
277. gbc.anchor = GridBagConstraints.EAST;
278. gbc.fill = GridBagConstraints.NONE;
279. add(new JLabel(columnName), gbc, 0, i - 1, 1, 1);
280.
281. gbc.weightx = 100;
282. gbc.anchor = GridBagConstraints.WEST;
283. gbc.fill = GridBagConstraints.HORIZONTAL;
284. add(tb, gbc, 1, i - 1, 1, 1);
285. }
286. }
287.
288. /**
289. Shows a database row by populating all text fields
290. with the column values.
291. */
292. public void showRow(ResultSet rs) throws SQLException
293. {
294. for (int i = 1; i <= fields.size(); i++)
295. {
296. String field = rs.getString(i);
297. JTextField tb
298. = (JTextField)fields.get(i - 1);
299. tb.setText(field);
300. }
301. }
302.
303. /**
304. Adds a component to this panel.
305. @param c the component to add
306. @param gbc the grid bag constraints
307. @param x the grid bax column
308. @param y the grid bag row
309. @param w the number of grid bag columns spanned
310. @param h the number of grid bag rows spanned
311. */
312. private void add(Component c,
313. GridBagConstraints gbc, int x, int y, int w, int h)
314. {
315. gbc.gridx = x;
316. gbc.gridy = y;
317. gbc.gridwidth = w;
318. gbc.gridheight = h;
319. add(c, gbc);
320. }
321.
322. private ArrayList fields;
323.}
![]()
DatabaseMetaData getMetaData()
returns the metadata for the connection as a DataBaseMetaData object.
![]()
ResultSet getTables(String catalog, String schemaPattern, String tableNamePattern, String types[])
gets a description of all tables in a catalog that match the schema and table name patterns and the type criteria. (A schema describes a group of related tables and access permissions. A catalog describes a related group of schemas. These concepts are important for structuring large databases.)
The catalog and schema parameters can be "" to retrieve those tables without a catalog or schema, or null to return tables regardless of catalog or schema.
The types array contains the names of the table types to include. Typical types are TABLE, VIEW, SYSTEM TABLE, GLOBAL TEMPORARY, LOCAL TEMPORARY, ALIAS, and SYNONYM. If types is null, then tables of all types are returned.
The result set has five columns, all of which are of type String, as shown in Table 4-10.
| 1 | TABLE_CAT | Table catalog (may be null) |
| 2 | TABLE_SCHEM | Table schema (may be null) |
| 3 | TABLE_NAME | Table name |
| 4 | TABLE_TYPE | Table type |
| 5 | REMARKS | Comment on the table |
int getJDBCMajorVersion()
int getJDBCMinorVersion()
(JDBC 3) Return the major and minor JDBC version numbers of the driver that established the database connection. For example, a JDBC 3.0 driver has major version number 3 and minor version number 0.
int getMaxStatements()
Returns the maximum number of concurrently open statements per database connection, or 0 if the number is unlimited or unknown.
![]()
ResultSetMetaData getMetaData()
gives you the metadata associated with the current ResultSet columns.
![]()
int getColumnCount()
returns the number of columns in the current ResultSet object.
int getColumnDisplaySize(int column)
tells you the maximum width of the column specified by the index parameter.
| Parameters: | column | the column number |
String getColumnLabel(int column)
gives you the suggested title for the column.
| Parameters: | column | the column number |
String getColumnName(int column)
gives the column name associated with the column index specified.
| Parameters: | column | the column number |
While we do not want to go deep into transaction support, we do want to show you how to group a set of statements to form a transaction that can be committed when all has gone well, or rolled back as if none of the commands had been issued if an error has occurred in one of them.
The major reason for grouping commands into transactions is database integrity. For example, suppose we want to add a new book to our book database. Then, it is important that we simultaneously update the Books, Authors, and BooksAuthors table. If the update were to add new rows into the first two tables but not into the third, then the books and authors would not be properly matched up.
If you group updates to a transaction, then the transaction either succeeds in its entirety and it can be committed, or it fails somewhere in the middle. In that case, you can carry out a rollback and the database automatically undoes the effect of all updates that occurred since the last committed transaction. Furthermore, queries are guaranteed to report only on the committed state of the database.
By default, a database connection is in autocommit mode, and each SQL command is committed to the database as soon as it is executed. Once a command is committed, you cannot roll it back.
To check the current autocommit mode setting, call the getAutoCommit method of the Connection class.
You turn off autocommit mode with the command
conn.setAutoCommit(false);
Now you create a statement object in the normal way:
Statement stat = conn.createStatement();
Call executeUpdate any number of times:
stat.executeUpdate(command1); stat.executeUpdate(command2); stat.executeUpdate(command3); . . .
When all commands have been executed, call the commit method:
conn.commit();
However, if an error occurred, call
conn.rollback();
Then, all commands until the last commit are automatically reversed. You typically issue a rollback when your transaction was interrupted by a SQLException.
Suppose a program needs to execute many INSERT statements to populate a database table. In JDBC 2, you can improve the performance of the program by using a batch update. In a batch update, a sequence of commands is collected and submitted as a batch.
NOTE
![]() |
Use the supportsBatchUpdates method of the DatabaseMetaData class to find out if your database supports this feature. |
The commands in a batch can be actions such as INSERT, UPDATE, and DELETE as well as data definition commands such as CREATE TABLE and DROP TABLE. However, you cannot add SELECT commands to a batch since executing a SELECT statement returns a result set.
To execute a batch, you first create a Statement object in the usual way:
Statement stat = conn.createStatement();
Now, instead of calling executeUpdate, you call the addBatch method:
String command = "CREATE TABLE . . ." stat.addBatch(command); while (. . .) { command = "INSERT INTO . . . VALUES (" + . . . + ")"; stat.addBatch(command); }
Finally, you submit the entire batch.
int[] counts = stat.executeBatch();
The call to executeBatch returns an array of the row counts for all submitted commands. (Recall that an individual call to executeUpdate returns an integer, namely, the count of the rows that are affected by the command.) In our example, the executeBatch method returns an array with first element equal to 0 (because the CREATE TABLE command yields a row count of 0) and all other elements equal to 1 (because each INSERT command affects one row).
For proper error handling in batch mode, you want to treat the batch execution as a single transaction. If a batch fails in the middle, you want to roll back to the state before the beginning of the batch.
First, turn autocommit mode off, then collect the batch, execute it, commit it, and finally restore the original autocommit mode:
boolean autoCommit = conn.getAutoCommit(); conn.setAutoCommit(false); Statement stat = conn.getStatement(); . . . // keep calling stat.addBatch(. . .); . . . stat.executeBatch(); conn.commit(); conn.setAutoCommit(autoCommit);
![]()
void setAutoCommit(boolean b)
sets the autocommit mode of this connection to b. If autocommit is true, all statements are committed as soon as their execution is completed.
boolean getAutoCommit()
gets the autocommit mode of this connection.
void commit()
commits all statements that were issued since the last commit.
void rollback()
undoes the effect of all statements that were issued since the last commit.
![]()
void addBatch(String command)
(JDBC 2) adds the command to the current batch of commands for this statement.
int[] executeBatch()
(JDBC 2) executes all commands in the current batch. Returns an array of row counts, containing an element for each command in the batch that denotes the number of rows affected by that command.
![]()
boolean supportsBatchUpdates()
(JDBC 2) returns true if the driver supports batch updates.
The simplistic database connection setup of the preceding sections, with a database.properties file, is suitable for small test programs, but it won't scale for larger applications.
When deploying a JDBC 2 application in an enterprise environment, the management of database connections is integrated with the Java Naming and Directory Interface (JNDI). A directory manages the location of data sources across the enterprise. Using a directory allows for centralized management of user names, passwords, database names, and JDBC URLs.
In such an environment, you use the following code to establish a database connection:
Context jndiContext = . . .;
DataSource source
= (DataSource)jndiContext.lookup("jdbc/corejava");
Connection conn = source.getConnection(username, password);
Note that the DriverManager is no longer involved. Instead, the JNDI service locates a data source. A data source is an interface that allows for simple JDBC connections as well as more advanced services, such as executing distributed transactions that involve multiple databases. The DataSource interface is defined in the javax.sql standard extension package.
Management of user names and logins is just one of the issues that require special attention. A second issue involves the cost of establishing database connections.
Our simple database programs established a single database connection at the start of the program and closed it at the end of the program. However, in many programming situations, this approach won't work. Consider a typical web application. Such an application serves multiple page requests in parallel. Multiple requests may need simultaneous access the database. With many databases, a connection is not intended to be shared by multiple threads. Thus, each request needs its own connection. A simplistic approach would be to establish a connection for each page request and close it afterwards. But that would be very costly. Establishing a database connection can be quite time-consuming. Connections are intended to be used for multiple queries, not to be closed after one or two queries.
The solution is to pool the connections. This means that database connections are not physically closed but are kept in a queue and reused. Connection pooling is an important service, and the JDBC specification provides hooks for implementors to supply it. However, the Java SDK itself does not provide any implementation, and database vendors don't usually include one with their JDBC driver either. Instead, vendors of application servers such as BEA WebLogic or IBM WebSphere supply connection pool implementations as part of the application server package.
Using a connection pool is completely transparent to the programmer. You acquire a connection from a source of pooled connections, by obtaining a data source and calling getConnection. When you are done using the connection, call close. That doesn't close the physical connection but tells the pool that you are done using it.
TIP
![]() |
If you deploy a simple Web application, using just a servlet container such as Apache Tomcat, you need to supply your own connection pool. Implementations of varying quality are available on the Internet. If you only need support for a single database login, and you don't use transactions, then the implementation at http://developer.java.sun.com/developer/onlineTraining/Programming/JDCBook/conpool.html should get you started. |
CAUTION
![]() |
In Web applications, you need to be particularly careful about recycling connections when an error occurs. Suppose an exception is thrown and the call to close is skipped. Then a user can drain the connection pool quickly simply by hitting the browser's "Reload" button in frustration, executing the same faulty service request over and over. Be sure to close the connections in finally clauses, like this: public void myService() throws SQLException
{
Connection conn = null;
try
{
conn = ds.getConnection();
. . .
}
finally
{
if (conn != null) conn.close();
}
}
This code has one flaw—if the conn.close()call throws an exception, it masks the original exception. That's an unfortunate consequence of the Java exception handling mechanism. However, saving and rethrowing the original exception would be fiendishly complex—we leave it as the proverbial exercise for the reader. |
You have now learned about the JDBC fundamentals and know enough to implement simple database applications. However, as we mentioned at the beginning of this chapter, databases are complex and there are quite a few advanced topics that are beyond the scope of an introductory chapter. For an overview of advanced JDBC capabilities, you can read the JDBC specifications at http://java.sun.com/products/jdbc.
| CONTENTS |